from datetime import date, datetime, timedelta
from decimal import Decimal
from typing import Optional, Tuple, List

from sqlalchemy import and_, desc, func, or_
from sqlalchemy.orm import Session

from backend.db.database import SessionLocal, create_table, print_table_name
from backend.models.member import Member
from backend.models.member_transaction import Transaction, TransactionType
from backend.schemas.member_pydantic import MemberCreate, MemberUpdate, SearchConditions, StatisticsResponse, \
    MemberResponse


class MemberService:
    """会员管理服务类"""

    def __init__(self, session_date: Session):
        self.session = session_date

    def generate_member_code(self):
        """生成会员编号"""
        today = date.today()
        date_str = today.strftime("%Y%m%d")

        # 查询今天已有的会员数量
        today_count = self.session.query(Member).filter(
            Member.registration_date == today
        ).count()

        # 生成编号：
        sequence = str(today_count + 1).zfill(6)
        return f"M{date_str}{sequence}"

    def create_member(self, member_data: MemberCreate) -> Member:
        """创建新会员"""
        # 检查手机号是否已存在
        existing_member = self.session.query(Member).filter(
            Member.phone == member_data.phone_num
        ).first()
        if existing_member:
            raise ValueError(f"手机号 {member_data.phone_num} 已存在")

        # 生成会员编号
        member_code = self.generate_member_code()

        # 创建会员对象
        db_member = Member(
            member_code=member_code,
            name=member_data.name,
            phone=member_data.phone_num,
            initial_balance=member_data.initial_balance,
            current_balance=member_data.initial_balance,
            note=member_data.note
        )

        self.session.add(db_member)
        self.session.commit()
        self.session.refresh(db_member)

        # 如果有初始余额，创建充值记录
        if member_data.initial_balance > 0:
            self._create_recharge_transaction(
                db_member.id,
                member_data.initial_balance,
                "初始充值"
            )

        return db_member

    def get_member_by_id(self, member_id: int) -> Optional[Member]:
        """根据ID获取会员,返回orm对象"""
        return self.session.query(Member).filter(Member.id == member_id).first()

    def get_member_by_code(self, member_code: str) -> Optional[Member]:
        """根据会员编号获取会员,返回orm对象"""
        return self.session.query(Member).filter(Member.member_code == member_code).first()

    def get_member_by_phone(self, phone: str) -> Optional[Member]:
        """根据手机号获取会员,返回orm对象"""
        return self.session.query(Member).filter(Member.phone == phone).first()

    def all_get_members(self, keyword=None):
        def return_list(data):
            list_member = []
            for result in data:
                result_1 = MemberResponse(
                    id=result.id,
                    name=result.name,
                    phone=result.phone,
                    registration_date=result.registration_date,
                    current_balance=result.current_balance,
                    last_consumption_date=result.last_consumption_date,
                    total_consumption=result.total_consumption,
                    consumption_count=result.consumption_count,
                    created_at=result.created_at,
                    updated_at=result.updated_at,
                    balance_status=result.balance_status,
                    description=result.note
                )
                list_member.append(result_1)
            return list_member

        if keyword is None:
            member_result = self.session.query(Member).all()
            return return_list(member_result)
        filters = []
        if str(keyword).isdigit():
            filters.append(Member.id == int(keyword))
        filters.append(Member.name.like(f"%{keyword}%"))
        filters.append(Member.phone.like(f"%{keyword}%"))
        query = self.session.query(Member).filter(
            or_(Member.id == keyword if str(keyword).isdigit() else False,  # 仅当输入是数字时匹配 ID
                Member.name.like(f"%{keyword}%"),
                Member.phone.like(f"%{keyword}%")))
        results = query.filter(or_(*filters)).all()
        return return_list(results)

    def update_member(self, member_id: int, member_data: MemberUpdate) -> Optional[Member]:
        """更新会员信息"""
        db_member = self.get_member_by_id(member_id)
        if not db_member:
            raise ValueError(f"没有此用户")
        # 检查手机号重复（如果更新了手机号）
        if member_data.phone_num and member_data.phone_num != db_member.phone:
            existing_phone = self.session.query(Member).filter(
                and_(Member.phone == member_data.phone_num, Member.id != member_id)
            ).first()
            if existing_phone:
                raise ValueError(f"手机号 {member_data.phone_num} 已存在")

        update_date = member_data.model_dump(exclude_unset=True)
        if 'name' in update_date:
            db_member.name = update_date['name']
        if 'phone_num' in update_date:
            db_member.phone = update_date['phone_num']
        if 'note' in update_date:
            db_member.note = update_date['note']

        self.session.commit()
        self.session.refresh(db_member)
        return db_member

    def delete_member(self, member_id: int) -> bool:
        """删除会员"""
        db_member = self.get_member_by_id(member_id)
        if not db_member:
            return False

        self.session.delete(db_member)
        self.session.commit()
        return True

    def get_members_count(self) -> int:
        """获取会员总数"""
        return self.session.query(Member).count()

    def get_statistics(self) -> StatisticsResponse:
        """获取会员统计信息"""
        # 总会员数
        total_members = self.session.query(Member).count()

        # 活跃会员数（最近30天有消费的会员）
        thirty_days_ago = date.today() - timedelta(days=30)
        active_members = self.session.query(Member).filter(
            Member.last_consumption_date >= thirty_days_ago
        ).count()

        # 总余额
        total_balance = self.session.query(func.sum(Member.current_balance)).scalar() or Decimal("0.00")

        # 总消费金额
        total_consumption = self.session.query(func.sum(Member.total_consumption)).scalar() or Decimal("0.00")

        # 今日新增会员
        today = date.today()
        today_registrations = self.session.query(Member).filter(
            Member.registration_date == today
        ).count()

        # 今日消费金额
        today_start = datetime.combine(today, datetime.min.time())
        today_end = datetime.combine(today, datetime.max.time())
        today_consumption = self.session.query(func.sum(Transaction.amount)).filter(
            and_(
                Transaction.transaction_type == TransactionType.CONSUMPTION,
                Transaction.transaction_date.between(today_start, today_end)
            )
        ).scalar() or Decimal("0.00")
        today_refund = self.session.query(func.sum(Transaction.amount)).filter(and_(
            Transaction.transaction_type == TransactionType.REFUND,
            Transaction.transaction_date.between(today_start, today_end)
        )).scalar() or Decimal("0.00")
        actual_consumption = today_consumption - today_refund

        return StatisticsResponse(
            total_members=total_members,
            active_members=active_members,
            total_balance=total_balance,
            total_consumption=total_consumption,
            today_registrations=today_registrations,
            today_consumption=actual_consumption
        )

    def _create_recharge_transaction(self, member_id: int, amount: Decimal, description: str = "充值"):
        """创建充值交易记录（内部方法）"""
        transaction = Transaction(
            member_id=member_id,
            transaction_type=TransactionType.RECHARGE,
            amount=amount,
            description=description
        )
        self.session.add(transaction)
        self.session.commit()
        return transaction


if __name__ == "__main__":
    with SessionLocal() as se:
        a = MemberService(se)
        b = a.all_get_members("123")
        print(b)
